package com.smfx.yxt.controller.sys;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.smfx.yxt.common.AdminUser;
import com.smfx.yxt.common.Constants;
import com.smfx.yxt.common.RspResult;
import com.smfx.yxt.common.enums.LateCoachType;
import com.smfx.yxt.common.enums.Status;
import com.smfx.yxt.common.enums.Week;
import com.smfx.yxt.controller.BaseController;
import com.smfx.yxt.model.FinalTermScoreReport;
import com.smfx.yxt.model.LateCoachPlan;
import com.smfx.yxt.model.SchoolCommentWeek;
import com.smfx.yxt.model.SysCommentTemplate;
import com.smfx.yxt.reqVo.BaseQueryVO;
import com.smfx.yxt.service.IFinalTermScoreReportService;
import com.smfx.yxt.service.ILateCoachService;
import com.smfx.yxt.service.ISchoolCommentWeekService;
import com.smfx.yxt.service.ISysCommentTemplateService;
import com.smfx.yxt.utils.CellUtil;
import com.smfx.yxt.utils.DateUtil;
import com.smfx.yxt.utils.StringUtil;

@RequestMapping("sys/file")
@Controller
public class FileController extends BaseController{
	
	private Logger logger = LoggerFactory.getLogger(FileController.class);

	@Autowired
	private ISysCommentTemplateService sysCommentTemplateService;
	@Autowired
	private ISchoolCommentWeekService schoolCommentWeekService;
	@Autowired
	private IFinalTermScoreReportService finalTermScoreReportService;
	@Autowired
	private ILateCoachService lateCoachService;
	
	@RequestMapping("commentTemplate/import")
	@ResponseBody Object commentTemplateImport(@RequestParam(value="files",required=false) MultipartFile buildInfo, String dictDataKey) {
		try {
			return saveCommentInfo(buildInfo, dictDataKey);
		} catch (Exception e) {
			logger.error("--commentTemplateImport, error={}", e);
		}
		return new RspResult(Status.TEMPLATE_ERROR);
	}
	
	@RequestMapping("weekTemplate/import")
	@ResponseBody Object weekTemplateImport(@RequestParam(value="files",required=false) MultipartFile buildInfo, Integer termId) {
		try {
			return saveWeekInfo(buildInfo, termId);
		} catch (Exception e) {
			logger.error("--weekTemplateImport, error={}", e);
		}
		return new RspResult(Status.TEMPLATE_ERROR);
	}
	
	@RequestMapping("classTermReport/import")
	@ResponseBody Object namelistImport(
			@RequestParam(value="files")MultipartFile buildInfo, BaseQueryVO vo) {
		try {
			if(vo.getTermId() == null) {
				return new RspResult(Status.FAIL.getCode(), "学期不能为空");
			}else if(vo.getGrade() == null) {
				return new RspResult(Status.FAIL.getCode(), "年级不能为空");
			}else if(vo.getClazz() == null) {
				return new RspResult(Status.FAIL.getCode(), "班级不能为空");
			}
			return saveClassTermReportBuildInfo(buildInfo, vo);
		} catch (Exception e) {
			logger.error("--namelistImport, error={}", e);
		}
		return new RspResult(Status.TEMPLATE_ERROR);
	}
	
	@RequestMapping("lateCoachPlan/import")
	@ResponseBody Object lateCoachPlanImport(
			@RequestParam(value="files")MultipartFile buildInfo) {
		try {
			return saveLateCoachPlanBuildInfo(buildInfo);
		} catch (Exception e) {
			logger.error("--lateCoachPlanImport, error={}", e);
		}
		return new RspResult(Status.TEMPLATE_ERROR);
	}
	
	public RspResult saveClassTermReportBuildInfo(MultipartFile buildInfo, BaseQueryVO vo) throws Exception{
    	// 创建一个FileInputStream 文件输入流
        InputStream inputStream = buildInfo.getInputStream();
    	// 创建对Excel工作簿文件的引用
        Workbook wookbook = null;
        String filename = buildInfo.getOriginalFilename();
        String fileType = filename.substring(filename.lastIndexOf(".") + 1,
        		filename.length());
        if (fileType.equals("xls")) {
        	try{
        		wookbook = new HSSFWorkbook(inputStream);
        	}catch(Exception e){
        		wookbook = null;
           	 	wookbook = new XSSFWorkbook(inputStream);
        	}
        }else{
       	 	wookbook = new XSSFWorkbook(inputStream);
        }
        // 在Excel文档中，第一张工作表的缺省索引是0
        Sheet sheet = wookbook.getSheetAt(0);
        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();// 获取总列数
        int rowNum=sheet.getPhysicalNumberOfRows();//获得总行数
        if(rowNum <= 1) {
        	return new RspResult(Status.TEMPLATE_DATA_NULL);
        }
        Map<Integer, String> map = new HashMap<>();
        Row headRow = sheet.getRow(0);
        for(int i = 0; i < coloumNum; i ++) {
        	Cell cell = headRow.getCell((short)i);
        	String val = CellUtil.getCellValue(cell);
        	if(StringUtils.isNotBlank(val)) {
        		map.put(i, StringUtil.removeSpecialChar(val));
        	}
        }
        List<FinalTermScoreReport> list = new ArrayList<>();
        // 遍历行 从第三行开始遍历
        for (int i = 1; i < rowNum; i++) {
        	// 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
            	FinalTermScoreReport scoreReport = new FinalTermScoreReport();
            	for(int j = 0; j < coloumNum; j ++) {
            		Cell cell = row.getCell((short)j);
            		if(cell != null) {
            			String val = null;
            			try {
            				val = CellUtil.getCellValue(cell);
						} catch (Exception e) {
							logger.error("---第" + i + "行的" + (j+1) + "异常， error={}", e.getMessage());
						}
            			if(StringUtils.isBlank(val)){
            				continue;
            			}
            			String name = map.get(j);
            			if("学号".equals(name)) scoreReport.setStudentNo(Integer.valueOf(val));
            			else if("姓名".equals(name)) scoreReport.setStudentName(val);
            			else if("政治平时成绩".equals(name)) scoreReport.setPoliticsNormalScore(scoreFixed(val));
    					else if("政治期末成绩".equals(name)) scoreReport.setPoliticsFinalTermScore(scoreFixed(val));
    					else if("政治总平成绩".equals(name)) scoreReport.setPoliticsTotalAvgScore(scoreFixed(val));
    					else if("政治期末级平均分".equals(name)) scoreReport.setPoliticsFinalTermGradeAvgScore(scoreFixed(val));
    					else if("语文平时成绩".equals(name)) scoreReport.setChineseNormalScore(scoreFixed(val));
    					else if("语文期末成绩".equals(name)) scoreReport.setChineseFinalTermScore(scoreFixed(val));
    					else if("语文总平成绩".equals(name)) scoreReport.setChineseTotalAvgScore(scoreFixed(val));
    					else if("语文期末级平均分".equals(name)) scoreReport.setChineseFinalTermGradeAvgScore(scoreFixed(val));
    					else if("语文口语".equals(name)) scoreReport.setChineseLanguage(val);
    					else if("数学平时成绩".equals(name)) scoreReport.setMathNormalScore(scoreFixed(val));
    					else if("数学期末成绩".equals(name)) scoreReport.setMathFinalTermScore(scoreFixed(val));
    					else if("数学总平成绩".equals(name)) scoreReport.setMathTotalAvgScore(scoreFixed(val));
    					else if("数学期末级平均分".equals(name)) scoreReport.setMathFinalTermGradeAvgScore(scoreFixed(val));
    					else if("英语平时成绩".equals(name)) scoreReport.setEnglishNormalScore(scoreFixed(val));
    					else if("英语期末成绩".equals(name)) scoreReport.setEnglishFinalTermScore(scoreFixed(val));
    					else if("英语总平成绩".equals(name)) scoreReport.setEnglishTotalAvgScore(scoreFixed(val));
    					else if("英语期末级平均分".equals(name)) scoreReport.setEnglishFinalTermGradeAvgScore(scoreFixed(val));
    					else if("英语口语".equals(name)) scoreReport.setEnglishLanguage(val);
    					else if("生物平时成绩".equals(name)) scoreReport.setBiologyNormalScore(scoreFixed(val));
    					else if("生物期末成绩".equals(name)) scoreReport.setBiologyFinalTermScore(scoreFixed(val));
    					else if("生物总平成绩".equals(name)) scoreReport.setBiologyTotalAvgScore(scoreFixed(val));
    					else if("生物期末级平均分".equals(name)) scoreReport.setBiologyFinalTermGradeAvgScore(scoreFixed(val));
    					else if("物理平时成绩".equals(name)) scoreReport.setPhyticsNormalScore(scoreFixed(val));
    					else if("物理期末成绩".equals(name)) scoreReport.setPhyticsFinalTermScore(scoreFixed(val));
    					else if("物理总平成绩".equals(name)) scoreReport.setPhyticsTotalAvgScore(scoreFixed(val));
    					else if("物理期末级平均分".equals(name)) scoreReport.setPhyticsFinalTermGradeAvgScore(scoreFixed(val));
    					else if("化学平时成绩".equals(name)) scoreReport.setChemistryNormalScore(scoreFixed(val));
    					else if("化学期末成绩".equals(name)) scoreReport.setChemistryFinalTermScore(scoreFixed(val));
    					else if("化学总平成绩".equals(name)) scoreReport.setChemistryTotalAvgScore(scoreFixed(val));
    					else if("化学期末级平均分".equals(name)) scoreReport.setChemistryFinalTermGradeAvgScore(scoreFixed(val));
    					else if("历史平时成绩".equals(name)) scoreReport.setHistoryNormalScore(scoreFixed(val));
    					else if("历史期末成绩".equals(name)) scoreReport.setHistoryFinalTermScore(scoreFixed(val));
    					else if("历史总平成绩".equals(name)) scoreReport.setHistoryTotalAvgScore(scoreFixed(val));
    					else if("历史期末级平均分".equals(name)) scoreReport.setHistoryFinalTermGradeAvgScore(scoreFixed(val));
    					else if("地理平时成绩".equals(name)) scoreReport.setGeographyNormalScore(scoreFixed(val));
    					else if("地理期末成绩".equals(name)) scoreReport.setGeographyFinalTermScore(scoreFixed(val));
    					else if("地理总平成绩".equals(name)) scoreReport.setGeographyTotalAvgScore(scoreFixed(val));
    					else if("地理期末级平均分".equals(name)) scoreReport.setGeographyFinalTermGradeAvgScore(scoreFixed(val));
    					else if("体育平时成绩".equals(name)) scoreReport.setSportsNormalScore(scoreFixed(val));
    					else if("体育期末成绩".equals(name)) scoreReport.setSportsFinalTermScore(scoreFixed(val));
    					else if("体育总平成绩".equals(name)) scoreReport.setSportsTotalAvgScore(scoreFixed(val));
    					else if("音乐总平成绩".equals(name)) scoreReport.setMusicTotalAvgScore(scoreFixed(val));
    					else if("美术总平成绩".equals(name)) scoreReport.setArtTotalAvgScore(scoreFixed(val));
    					else if("信息平时成绩".equals(name)) scoreReport.setInfomationNormalScore(scoreFixed(val));
    					else if("信息期末成绩".equals(name)) scoreReport.setInfomationFinalTermScore(scoreFixed(val));
    					else if("信息总平成绩".equals(name)) scoreReport.setInfomationTotalAvgScore(scoreFixed(val));
    					else if("综合总平成绩".equals(name)) scoreReport.setSynthesizeTotalAvgScore(scoreFixed(val));
    					else if("操行分".equals(name)) scoreReport.setConductScore(scoreFixed(val));
    					else if("德育等级".equals(name)) scoreReport.setMoralEducationLevel(val);
    					else if("获奖情况".equals(name)) scoreReport.setPrizeItems(val);
    					else if("评语".equals(name)) scoreReport.setTeacherComment(val);
            		}
            	}
            	list.add(scoreReport);
            }
        }
        return finalTermScoreReportService.addImportTermScoreReport(list, vo);
    }
	
	public RspResult saveCommentInfo(MultipartFile buildInfo, String dictDataKey) throws Exception{
    	// 创建一个FileInputStream 文件输入流
        InputStream inputStream = buildInfo.getInputStream();
    	// 创建对Excel工作簿文件的引用
        Workbook wookbook = null;
        String filename = buildInfo.getOriginalFilename();
        String fileType = filename.substring(filename.lastIndexOf(".") + 1,
        		filename.length());
        if (fileType.equals("xls")) {
        	try{
        		wookbook = new HSSFWorkbook(inputStream);
        	}catch(Exception e){
        		wookbook = null;
           	 	wookbook = new XSSFWorkbook(inputStream);
        	}
        }else{
       	 	wookbook = new XSSFWorkbook(inputStream);
        }
        // 在Excel文档中，第一张工作表的缺省索引是0
        Sheet sheet = wookbook.getSheetAt(0);
        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();// 获取总列数
        int rowNum=sheet.getPhysicalNumberOfRows();//获得总行数
        if(rowNum <= 1) {
        	return new RspResult(Status.TEMPLATE_DATA_NULL);
        }
        List<SysCommentTemplate> list = new ArrayList<>();
        // 遍历行 从第三行开始遍历
        for (int i = 1; i < rowNum; i++) {
        	// 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
            	SysCommentTemplate signInfo = new SysCommentTemplate();
            	signInfo.setDictDataKey(dictDataKey);
            	for(int j = 0; j < coloumNum; j ++) {
            		Cell cell = row.getCell((short)j);
            		if(cell != null) {
            			String val = CellUtil.getCellValue(cell);
            			if(j == 0) signInfo.setTemplateContent(val);
            		}
            	}
            	list.add(signInfo);
            }
        }
        sysCommentTemplateService.insertBatch(list);
        return new RspResult(Status.SUCCESS);
    }
	
	public RspResult saveWeekInfo(MultipartFile buildInfo, Integer termId) throws Exception{
		AdminUser user = (AdminUser) request.getSession().getAttribute(Constants.ADMIN_USER);
		// 创建一个FileInputStream 文件输入流
        InputStream inputStream = buildInfo.getInputStream();
    	// 创建对Excel工作簿文件的引用
        Workbook wookbook = null;
        String filename = buildInfo.getOriginalFilename();
        String fileType = filename.substring(filename.lastIndexOf(".") + 1,
        		filename.length());
        if (fileType.equals("xls")) {
        	try{
        		wookbook = new HSSFWorkbook(inputStream);
        	}catch(Exception e){
        		wookbook = null;
           	 	wookbook = new XSSFWorkbook(inputStream);
        	}
        }else{
       	 	wookbook = new XSSFWorkbook(inputStream);
        }
        // 在Excel文档中，第一张工作表的缺省索引是0
        Sheet sheet = wookbook.getSheetAt(0);
        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();// 获取总列数
        int rowNum=sheet.getPhysicalNumberOfRows();//获得总行数
        if(rowNum <= 1) {
        	return new RspResult(Status.TEMPLATE_DATA_NULL);
        }
        List<SchoolCommentWeek> list = new ArrayList<>();
        // 遍历行 从第三行开始遍历
        for (int i = 1; i < rowNum; i++) {
        	// 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
            	SchoolCommentWeek signInfo = new SchoolCommentWeek();
            	signInfo.setSchoolId(user.getTeacherInfo().getSchoolId());
            	signInfo.setTermId(termId);
            	for(int j = 0; j < coloumNum; j ++) {
            		Cell cell = row.getCell((short)j);
            		if(cell != null) {
            			String val = CellUtil.getCellValue(cell);
            			if(j == 0) signInfo.setWeekNo(Integer.valueOf(val));
            			else if(j == 1) signInfo.setStartDate(DateUtil.dateToStr(DateUtil.strToDate(val, DateUtil.DEFAULT_DATE_FORMAT3),
            					DateUtil.DEFAULT_DATE_FORMAT3));
            			else if(j == 2) signInfo.setEndDate(DateUtil.dateToStr(DateUtil.strToDate(val, DateUtil.DEFAULT_DATE_FORMAT3),
            					DateUtil.DEFAULT_DATE_FORMAT3));
            		}
            	}
            	list.add(signInfo);
            }
        }
        schoolCommentWeekService.insertBatch(list);
        return new RspResult(Status.SUCCESS);
    }
	
	public RspResult saveLateCoachPlanBuildInfo(MultipartFile buildInfo) throws Exception{
		// 创建一个FileInputStream 文件输入流
        InputStream inputStream = buildInfo.getInputStream();
    	// 创建对Excel工作簿文件的引用
        Workbook wookbook = null;
        String filename = buildInfo.getOriginalFilename();
        String fileType = filename.substring(filename.lastIndexOf(".") + 1,
        		filename.length());
        if (fileType.equals("xls")) {
        	try{
        		wookbook = new HSSFWorkbook(inputStream);
        	}catch(Exception e){
        		wookbook = null;
           	 	wookbook = new XSSFWorkbook(inputStream);
        	}
        }else{
       	 	wookbook = new XSSFWorkbook(inputStream);
        }
        // 在Excel文档中，第一张工作表的缺省索引是0
        Sheet sheet = wookbook.getSheetAt(0);
        int coloumNum=sheet.getRow(0).getPhysicalNumberOfCells();// 获取总列数
        int rowNum=sheet.getPhysicalNumberOfRows();//获得总行数
        if(rowNum <= 1) {
        	return new RspResult(Status.TEMPLATE_DATA_NULL);
        }
        List<LateCoachPlan> list = new ArrayList<>();
        List<String> errorList = new ArrayList<>();
        // 遍历行 从第三行开始遍历
        for (int i = 1; i < rowNum; i++) {
        	// 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
            	LateCoachPlan plan = new LateCoachPlan();
            	for(int j = 0; j < coloumNum; j ++) {
            		Cell cell = row.getCell((short)j);
            		if(cell != null) {
            			String val = CellUtil.getCellValue(cell);
            			if(StringUtils.isBlank(val)) {
            				errorList.add("第" + (i + 1) + "行第" + (j + 1) + "列的值为空");
            				break;
            			}
            			if(j == 0) {
            				Integer key = Week.getKey(val.trim());
            				if(key == null) {
            					errorList.add("第" + (i + 1) + "行第" + (j + 1) + "列的值有误");
            					break;
            				}
            				plan.setWeek(key);
            			} else if(j == 1) {
            				plan.setClassId(Integer.valueOf(val));
            			} else if(j == 2) {
            				Integer key = LateCoachType.getKey(val.trim());
            				if(key == null) {
            					errorList.add("第" + (i + 1) + "行第" + (j + 1) + "列的值有误");
            					break;
            				}
            				plan.setCoachType(key);
            			} else if(j == 3) {
            				plan.setTeacherName(val.trim());
            			}
            		}
            	}
            	list.add(plan);
            }
        }
        if(!StringUtil.isNullOrEmpty(errorList)) {
        	return new RspResult(Status.FAIL.getCode(), String.join(",", errorList));
        }
        lateCoachService.insertPlanBatch(list);
        return new RspResult(Status.SUCCESS);
    }

	private static String scoreFixed(String v) {
		if(StringUtils.isNotBlank(v)) {
			int i = v.indexOf('.');
			if(i != -1 && (i + 3) < v.length()) {
				return v.substring(0, v.indexOf('.') + 3);
			}
		}
		return v;
	}
	
	public static void main(String[] args) {
		try {
			System.out.println(scoreFixed("B"));
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}